import sys
import os
from sqlalchemy import text

# Add current directory to path so we can import app
sys.path.append(os.getcwd())

from app.db import engine

def migrate():
    with engine.connect() as conn:
        print("Checking schema...")
        
        # 1. Add plan_id to review_tasks if not exists
        try:
            # Check if column exists (SQLite specific pragma)
            result = conn.execute(text("PRAGMA table_info(review_tasks)")).fetchall()
            columns = [row[1] for row in result]
            
            if "plan_id" not in columns:
                print("Adding plan_id column to review_tasks...")
                conn.execute(text("ALTER TABLE review_tasks ADD COLUMN plan_id VARCHAR(50)"))
                conn.execute(text("CREATE INDEX ix_review_tasks_plan_id ON review_tasks (plan_id)"))
                print("Added plan_id column.")
            else:
                print("plan_id column already exists.")
        except Exception as e:
            print(f"Error checking/adding plan_id: {e}")

        # 2. Create review_logs table if not exists
        try:
            # Check if table exists
            result = conn.execute(text("SELECT name FROM sqlite_master WHERE type='table' AND name='review_logs'")).fetchone()
            
            if not result:
                print("Creating review_logs table...")
                conn.execute(text("""
                    CREATE TABLE review_logs (
                        id INTEGER NOT NULL PRIMARY KEY,
                        knowledge_id INTEGER NOT NULL,
                        card_id VARCHAR(50) NOT NULL,
                        reviewed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        owner_id INTEGER NOT NULL,
                        FOREIGN KEY(knowledge_id) REFERENCES knowledge(id) ON DELETE CASCADE,
                        FOREIGN KEY(owner_id) REFERENCES users(id)
                    )
                """))
                conn.execute(text("CREATE INDEX ix_review_logs_knowledge_id ON review_logs (knowledge_id)"))
                print("Created review_logs table.")
            else:
                print("review_logs table already exists.")
        except Exception as e:
            print(f"Error creating review_logs table: {e}")
            
        conn.commit()
        print("Migration complete.")

if __name__ == "__main__":
    migrate()
